import logging

import pymssql
from database.database_conf import DatabaseConf
from database.database_object import DatabaseObject


class MssqlObject(DatabaseObject):
    def connect(self, db_conf: DatabaseConf) -> bool:
        ret = True
        try:
            self.db_url = f"Mssql[{db_conf.db_address}:{db_conf.db_port}/{db_conf.db_name}][{db_conf.db_username}][{db_conf.db_password}]"
            logging.info(self.db_url)
            self.db_con = pymssql.connect(server=db_conf.db_address,
                                          port=str(db_conf.db_port),
                                          user=db_conf.db_username,
                                          password=db_conf.db_password,
                                          database=db_conf.db_name,
                                          tds_version="7.0",
                                          timeout=5,
                                          charset='utf8'
                                          )
        except Exception as ex:
            ret = False
            self.last_error = str(ex)
            logging.error(ex)
        return ret

    def get_version(self) -> str:
        sql = "SELECT SERVERPROPERTY('ProductVersion') AS DB_VERSION"
        row = self.fetchone(sql)
        if row:
            return row[0]
        return ""

    def get_schema_list(self) -> list:
        schema_list = []
        sql = "SELECT name AS DB_NAME FROM sys.databases ORDER BY name"
        rows = self.fetchall(sql)
        for row in rows:
            schema_list.append(row[0])
        return schema_list

    def get_table_list(self, schema_name: str) -> list:
        table_list = []
        sql = "SELECT name AS TABLE_NAME FROM %s.sys.objects where  type='U' ORDER BY name" % schema_name
        rows = self.fetchall(sql, params=[schema_name])
        for row in rows:
            table_list.append(row[0])
        return table_list

    def get_filed_list(self, schema_name: str, table_name: str) -> list:
        filed_list = []
        sql = "use %s;select column_name as field_name ,data_type as field_type ,CHARACTER_MAXIMUM_LENGTH as field_length,'' as field_desc from INFORMATION_SCHEMA.COLUMNS WHERE table_catalog='%s' and table_name='%s' ORDER BY ordinal_position" % (
            schema_name, schema_name, table_name)
        rows = self.fetchall(sql, params=[schema_name, table_name])
        for row in rows:
            print(row)
            filed_row = dict()
            filed_row["field_name"] = row[0].lower()
            if row[2]:
                field_length = int(row[2])
                filed_row["field_length"] = field_length
                filed_row["field_type"] = f"{row[1]}({field_length})"
            else:
                filed_row["field_length"] = None
                filed_row["field_type"] = row[1]
            filed_row["field_desc"] = row[3]

            filed_list.append(filed_row)
        return filed_list
